package models

import (
	"errors"
	"fmt"
	"strconv"
	"strings"

	"cvevulner/common"

	"github.com/astaxie/beego/logs"
	"github.com/astaxie/beego/orm"
)

//ExcelPackage Released packages
type GaussExcelTag struct {
	PubTime  string
	Repo     string
	Packages string
}

// Provide gauss related data and mysql interface layer processing logic
func QueryDownloadFile(status int) (FileData []OpenGaussDownloadFile, err error) {
	o := orm.NewOrm()
	sql := fmt.Sprintf(`SELECT * from cve_open_gauss_download_file 
WHERE status = %d order by repo_version desc`, status)
	_, err = o.Raw(sql).QueryRows(&FileData)
	logs.Info("QueryDownloadFile, err: ", err)
	return
}

func UpdateDownloadFile(fileId int64, status int) {
	o := orm.NewOrm()
	err := o.Raw("update cve_open_gauss_download_file set status = ?,"+
		"update_time = ? where file_id = ?", status, common.GetCurTime(), fileId).QueryRow()
	logs.Info("UpdateDownloadFile", err)
}

// Query all processed issues and filter out the unaffected data that needs to be processed
func QueryGaussIssueData(startTime string) (issueTemp []IssueTemplate, err error) {
	sql := `SELECT * FROM cve_issue_template WHERE status = 3 AND issue_status in (2,6) AND 
cve_id IN (SELECT DISTINCT cve_id FROM cve_vuln_center WHERE cve_status = 2 and 
organizate_id = 2 and is_export != 1) AND create_time >= '%s'`
	sql = fmt.Sprintf(sql, startTime)
	o := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&issueTemp)
	return
}

// Get published CVE data
func QueryReleaseGaussIssue(ogv *OpenGaussVersion, field ...string) error {
	o := orm.NewOrm()
	err := o.Read(ogv, field...)
	return err
}

//GetCanExportExcelData Get exportable data
func QueryGaussExportSaData(cveNum, issueNum, repo string, issueId int64) (list []ExcelExport, err error) {
	if cveNum == "" {
		return list, errors.New("cve number can not empty")
	}
	sql := `SELECT b.num,c.*,a.issue_num,a.owned_component,a.cve_brief,
d.sec_id,d.introduction,d.summary,d.theme,d.description,d.influence_component,
d.affect_product,d.reference_link,d.affect_status,
e.public_date,e.openeuler_sa_num,a.cve_level,b.organizate_id,a.affected_version,a.issue_label,a.repo   
FROM cve_issue_template a 
RIGHT JOIN 
(SELECT (SELECT COUNT(*) FROM cve_vuln_center  WHERE cve_num = ? AND 
is_export != 1 AND pack_name = ? AND organizate_id = 2) num ,
bc.cve_id,bc.cve_num,bc.organizate_id 
FROM cve_vuln_center bc WHERE bc.cve_num = ? AND bc.is_export != 1 AND 
bc.pack_name = ? AND bc.organizate_id = 2) b
ON a.cve_id = b.cve_id
LEFT JOIN cve_score c
ON b.cve_id = c.cve_id
LEFT JOIN cve_security_notice d
ON b.cve_id = d.cve_id
LEFT JOIN cve_open_euler_s_a e
ON b.cve_id = e.cve_id
WHERE a.issue_num = ? and a.issue_id = ? and b.organizate_id = 2
`
	o := orm.NewOrm()
	_, err = o.Raw(sql, cveNum, repo, cveNum, repo, issueNum, issueId).QueryRows(&list)
	return
}

func QueryReleaseCve(ogc *OpenGaussCveList, field ...string) error {
	o := orm.NewOrm()
	err := o.Read(ogc, field...)
	return err
}

// update data
func UpdateReleaseCve(ogc *OpenGaussCveList, fields ...string) error {
	o := orm.NewOrm()
	_, err := o.Update(ogc, fields...)
	return err
}

// insert data
func InsertReleaseCve(ogc *OpenGaussCveList) (int64, error) {
	o := orm.NewOrm()
	id, err := o.Insert(ogc)
	return id, err
}

func QueryGaussVersion(ogv *OpenGaussVersion, field ...string) error {
	o := orm.NewOrm()
	err := o.Read(ogv, field...)
	return err
}

func UpdateGaussVersion(ogv *OpenGaussVersion, fields ...string) error {
	o := orm.NewOrm()
	_, err := o.Update(ogv, fields...)
	return err
}

// insert data
func InsertGaussVersion(ogv *OpenGaussVersion) (int64, error) {
	o := orm.NewOrm()
	id, err := o.Insert(ogv)
	return id, err
}

func GetGaussIssueNumber(packName string) (issueTemp []IssueTemplate, err error) {
	sql := `select * from cve_issue_template where status = 3 and issue_status = 2 and 
cve_id in (select cve_id from cve_vuln_center where cve_status = 2 and 
is_export in (0,3) and repo_name in ('%s') and organizate_id = 2)`
	sql = fmt.Sprintf(sql, packName)
	o := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&issueTemp)
	return
}

func QuerySaDataByPackName(ogl *OpenGaussListTemp) error {
	sql := `select * from cve_open_gauss_list_temp where influence_component = '%s' and affect_product = '%s'`
	sql = fmt.Sprintf(sql, ogl.InfluenceComponent, ogl.AffectProduct)
	o := orm.NewOrm()
	err := o.Raw(sql).QueryRow(ogl)
	if ogl.GaussTempId > 0 {
		return nil
	} else {
		return err
	}
}

func UpdateSaData(ogl *OpenGaussListTemp, fields ...string) error {
	o := orm.NewOrm()
	_, err := o.Update(ogl, fields...)
	return err
}

// insert data
func InsertSaData(ogl *OpenGaussListTemp) (int64, error) {
	o := orm.NewOrm()
	id, err := o.Insert(ogl)
	return id, err
}

func GetGaussExportThem(cveNums, component, affectBranch string) (string, error) {
	if cveNums == "" || component == "" {
		return "", errors.New("param is empty")
	}
	s := strings.Split(cveNums, ";")
	for k, v := range s {
		s[k] = "'" + v + "'"
	}
	cveNums = strings.Join(s, ",")
	type tmp = struct {
		OpenEulerScore float64 `orm:"column(openeuler_score)"`
		Theme          string  `orm:"column(theme)"`
		AffectProduct  string  `orm:"column(affect_product)"`
	}
	res := make([]tmp, 0)
	sql := fmt.Sprintf(`SELECT b.openeuler_score,a.theme,a.affect_product FROM cve_issue_template b 
JOIN cve_security_notice a ON a.cve_id = b.cve_id
WHERE b.cve_num IN (%s)
AND b.owned_component = '%s'`, cveNums, component)
	o := orm.NewOrm()
	_, err := o.Raw(sql).QueryRows(&res)
	if err != nil {
		return "", err
	}
	max := float64(0)
	resStr := ""
	for _, v := range res {
		if v.AffectProduct == "" || len(v.AffectProduct) < 2 {
			continue
		}
		if max < v.OpenEulerScore {
			max = v.OpenEulerScore
			if affectBranch != "" && len(affectBranch) > 1 {
				resStr = strings.ReplaceAll(v.Theme, v.AffectProduct, affectBranch)
			} else {
				resStr = v.Theme
			}
		}
	}
	return resStr, nil
}

func QueryOpenGaussGroup(ogg *OpenGaussGroup, field ...string) error {
	o := orm.NewOrm()
	err := o.Read(ogg, field...)
	return err
}

// insert data
func InsertOpenGaussGroup(ogg *OpenGaussGroup) (int64, error) {
	o := orm.NewOrm()
	id, err := o.Insert(ogg)
	return id, err
}

func QueryOpenGaussPlatform(ogp *OpenGaussPlatform, field ...string) error {
	o := orm.NewOrm()
	err := o.Read(ogp, field...)
	return err
}

// insert data
func InsertOpenGaussPlatform(ogp *OpenGaussPlatform) (int64, error) {
	o := orm.NewOrm()
	id, err := o.Insert(ogp)
	return id, err
}

func QueryOpenGaussPackage(ogk *OpenGaussPackage, field ...string) error {
	o := orm.NewOrm()
	err := o.Read(ogk, field...)
	return err
}

// insert data
func InsertOpenGaussPackage(ogk *OpenGaussPackage) (int64, error) {
	o := orm.NewOrm()
	id, err := o.Insert(ogk)
	return id, err
}

func UpdateOpenGaussPackage(ogk *OpenGaussPackage, fields ...string) error {
	o := orm.NewOrm()
	_, err := o.Update(ogk, fields...)
	return err
}

func QueryGaussSaMaxValue(ogs *OpenGaussSiteList) (err error) {
	sql := `select * from cve_open_gauss_site_list where status = 3 order by gauss_id desc limit 1`
	o := orm.NewOrm()
	err = o.Raw(sql).QueryRow(ogs)
	return
}

func QueryOpenGaussSitePreAll() (ogl []OpenGaussSiteList, err error) {
	sql := `select * from cve_open_gauss_site_list where status != 3`
	o := orm.NewOrm()
	num, err := o.Raw(sql).QueryRows(&ogl)
	logs.Info("QueryOpenGaussSitePreAll, err: ", err, num)
	return
}

func QuerySaTempByPackName(packName string) (ogl []OpenGaussListTemp, err error) {
	sql := `select * from cve_open_gauss_list_temp where influence_component = '%s' and status = 1`
	sql = fmt.Sprintf(sql, packName)
	o := orm.NewOrm()
	num, err := o.Raw(sql).QueryRows(&ogl)
	logs.Info("QuerySaTempByPackName, err: ", err, num)
	return
}

func DeleteSaTempByPackName(packName string) {
	o := orm.NewOrm()
	err := o.Raw("delete from cve_open_gauss_list_temp where influence_component = ?", packName).QueryRow()
	logs.Info("DeleteSaTempByPackName", err)
}

func QueryOpenGaussCveByGid(gaussId int64) (ogl []OpenGaussCveList, err error) {
	sql := ""
	if gaussId == 0 {
		sql = fmt.Sprintf(`select * from cve_open_gauss_cve_list where status != 3`)
	} else {
		sql = fmt.Sprintf(`select * from cve_open_gauss_cve_list where gauss_id = %d and status != 3`, gaussId)
	}
	o := orm.NewOrm()
	num, err := o.Raw(sql).QueryRows(&ogl)
	logs.Info("QueryOpenGaussCveByGid, err: ", err, num)
	return
}

func DeleteOpenGaussVersionById(cveId int64) {
	o := orm.NewOrm()
	err := o.Raw("delete from cve_open_gauss_version where cve_id = ?", cveId).QueryRow()
	logs.Info("DeleteOpenGaussVersionById", err)
}

func DeleteGaussVersionByIdStatus(cveId int64) {
	o := orm.NewOrm()
	err := o.Raw("delete from cve_open_gauss_version where cve_id = ? and release_flag = 1", cveId).QueryRow()
	logs.Info("DeleteGaussVersionByIdStatus", err)
}

func DeleteOpenGaussCveByGid(gaussId int64) {
	o := orm.NewOrm()
	err := o.Raw("delete from cve_open_gauss_cve_list where gauss_id = ?", gaussId).QueryRow()
	logs.Info("DeleteOpenGaussCveById", err)
}

func DeleteOpenGaussCveByCid(cveId int64) {
	o := orm.NewOrm()
	err := o.Raw("delete from cve_open_gauss_cve_list where id = ?", cveId).QueryRow()
	logs.Info("DeleteOpenGaussCveByCid", err)
}

func DeleteOpenGaussPackByGid(gaussId int64) {
	o := orm.NewOrm()
	err := o.Raw("delete from cve_open_gauss_package where gauss_id = ?", gaussId).QueryRow()
	logs.Info("DeleteOpenGaussPackByGid", err)
}

func DeleteOpenGaussSitePreAll() {
	o := orm.NewOrm()
	err := o.Raw("delete from cve_open_gauss_site_list where status != 3").QueryRow()
	logs.Info("DeleteOpenGaussSitePreAll", err)
}

// insert data
func InsertOpenGaussSiteList(osl *OpenGaussSiteList) (int64, error) {
	o := orm.NewOrm()
	id, err := o.Insert(osl)
	return id, err
}

func UpdateOpenGaussCveByGid(gaussId, sGaussId int64) {
	o := orm.NewOrm()
	err := o.Raw("update cve_open_gauss_cve_list set gauss_id =?,update_time = ? where gauss_id = ?",
		sGaussId, common.GetCurTime(), gaussId).QueryRow()
	logs.Info("UpdateOpenGaussCveByGid", err)
}

func UpdateOpenGaussVersionByCid(tempCveId, cveId int64, cveNum string) {
	o := orm.NewOrm()
	err := o.Raw("update cve_open_gauss_version set cve_id =?,update_time = ? where cve_id = ? and cve_num = ?",
		cveId, common.GetCurTime(), tempCveId, cveNum).QueryRow()
	logs.Info("UpdateOpenGaussVersionByCid", err)
}

func UpdateOpenGaussPackByGid(gaussId, sGaussId int64) {
	o := orm.NewOrm()
	err := o.Raw("update cve_open_gauss_package set gauss_id = ?,update_time = ? where gauss_id = ?",
		sGaussId, common.GetCurTime(), gaussId).QueryRow()
	logs.Info("UpdateOpenGaussPackByGid", err)
}

// Upgrade pre-release data to official data
func UpdateGaussPreTRelease() {
	o := orm.NewOrm()
	siteErr := o.Raw("update cve_open_gauss_site_list set status = 3,update_time = ? where status = 1",
		common.GetCurTime()).QueryRow()
	if siteErr != nil {
		logs.Error("UpdateGaussPreTRelease, update cve_open_gauss_site_list failed ,err: ", siteErr)
	}
	o = orm.NewOrm()
	cveErr := o.Raw("update cve_open_gauss_cve_list set status = 3,update_time = ? where status = 1",
		common.GetCurTime()).QueryRow()
	if cveErr != nil {
		logs.Error("UpdateGaussPreTRelease, update cve_open_gauss_cve_list failed ,err: ", cveErr)
	}
}

func QueryGaussSaCount(cveLevel, years, releaseFlag int, searchName string) (count int64) {
	res := struct {
		Total int64
	}{}
	o := orm.NewOrm()
	qb, _ := orm.NewQueryBuilder("mysql")
	qb = qb.Select("COUNT(gauss_id) total").From("cve_open_gauss_site_list")
	if releaseFlag == 2 {
		qb = qb.Where("status = 3")
	} else {
		qb = qb.Where("status = 1")
	}
	if cveLevel > 0 {
		qb = qb.And("cve_level_value = " + strconv.Itoa(cveLevel))
	}
	if years > 0 {
		qb = qb.And("sa_years = " + strconv.Itoa(years))
	}

	if len(searchName) > 1 {
		qb = qb.And("(gauss_sa_num like ? OR influence_component like ?)")
	}
	var err error
	if len(searchName) > 1 {
		err = o.Raw(qb.String(), "%"+searchName+"%","%"+searchName+"%").QueryRow(&res)
	} else {
		err = o.Raw(qb.String()).QueryRow(&res)
	}
	if err != nil {
		logs.Error("QueryGaussSaCount, err: ", err)
		return 0
	}
	return res.Total
}

//QueryIssue query QueryOpenGaussSiteList
func QueryOpenGaussSiteList(pageNum, pageSize, cveLevel, years,
	releaseFlag int, searchName string) ([]OpenGaussSiteList, error) {
	startSize := (pageNum - 1) * pageSize
	o := orm.NewOrm()
	var psl []OpenGaussSiteList
	qb, _ := orm.NewQueryBuilder("mysql")
	qb = qb.Select("*").From("cve_open_gauss_site_list")
	if releaseFlag == 2 {
		qb = qb.Where("status = 3")
	} else {
		qb = qb.Where("status = 1")
	}
	if cveLevel > 0 {
		qb = qb.And("cve_level_value = " + strconv.Itoa(cveLevel))
	}
	if years > 0 {
		qb = qb.And("sa_years = " + strconv.Itoa(years))
	}

	if len(searchName) > 1 {
		qb = qb.And("(gauss_sa_num like ? OR influence_component like ?)")
	}
	qb = qb.OrderBy("gauss_id desc").Limit(pageSize).Offset(startSize)
	var err error
	var num int64
	if len(searchName) > 1 {
		num, err = o.Raw(qb.String(), "%"+searchName+"%","%"+searchName+"%").QueryRows(&psl)
	} else {
		num, err = o.Raw(qb.String()).QueryRows(&psl)
	}
	if err == nil && num > 0 {
		logs.Info("QueryOpenGaussSiteList, search num: ", num)
	} else {
		logs.Info("QueryOpenGaussSiteList, cur_time:",
			common.GetCurTime(), ",err: ", err)
	}
	return psl, err
}

func QueryOpenGaussSaByNum(osl *OpenGaussSiteList, field ...string) error {
	o := orm.NewOrm()
	err := o.Read(osl, field...)
	return err
}

func QueryGaussPackageByGid(gaussId int64, repoVersion string) (ogl []OpenGaussPackage, err error) {
	sql := `select * from cve_open_gauss_package where gauss_id = %d and repo_version = '%s' and status = 1`
	sql = fmt.Sprintf(sql, gaussId, repoVersion)
	o := orm.NewOrm()
	num, err := o.Raw(sql).QueryRows(&ogl)
	logs.Info("QueryGaussPackageByGid, err: ", err, num)
	return
}

func QueryGaussPlatByPids(platIds []int64) (ogl []OpenGaussPlatform, err error) {
	platStr := "("
	if len(platIds) > 0 {
		for _, pid := range platIds {
			platStr = platStr + strconv.FormatInt(pid, 10) + ","
		}
		platStr = platStr[:len(platStr)-1] + ")"
		sql := fmt.Sprintf(`select * from cve_open_gauss_platform where plat_id in %s and status = 1`, platStr)
		o := orm.NewOrm()
		num, err := o.Raw(sql).QueryRows(&ogl)
		logs.Info("QueryGaussPackageByGid, err: ", err, num)
	}
	return
}

func QueryGaussCveCount(releaseFlag, isAffectFlag int, searchName string) (count int64) {
	SearchGaussCondCve(releaseFlag, isAffectFlag, searchName)
	o := orm.NewOrm()
	q := o.QueryTable("cve_open_gauss_cve_list")
	if len(searchName) > 1 {
		q = q.Filter("cve_num__icontains", searchName)
	}
	if releaseFlag == 2 {
		q = q.Filter("status", 3)
	} else {
		q = q.Filter("status", 1)
	}
	if isAffectFlag == 3 {
		q = q.Filter("gauss_id", 0)
	} else if isAffectFlag == 2{
		q = q.Filter("gauss_id__gt", 0)
	}
	num, err := q.Count()
	if err != nil {
		logs.Error("QueryGaussCveCount, err: ", err)
		return 0
	}
	return num
}

// query QueryOpenGaussCveList
func QueryOpenGaussCveList(pageNum, pageSize, releaseFlag,
	isAffectFlag int, searchName string) ([]OpenGaussCveList, error) {
	startSize := (pageNum - 1) * pageSize
	o := orm.NewOrm()
	var psl []OpenGaussCveList
	q := o.QueryTable("cve_open_gauss_cve_list")
	if len(searchName) > 1 {
		q = q.Filter("cve_num__icontains", searchName)
	}
	if releaseFlag == 2 {
		q = q.Filter("status", 3)
	} else {
		q = q.Filter("status", 1)
	}
	if isAffectFlag == 3 {
		q = q.Filter("gauss_id", 0)
	} else if isAffectFlag == 2{
		q = q.Filter("gauss_id__gt", 0)
	}
	num,err := q.OrderBy("-id").Limit(pageSize).Offset(startSize).All(&psl)
	if err == nil && num > 0 {
		logs.Info("QueryOpenGaussCveList, search num: ", num)
	} else {
		logs.Info("QueryOpenGaussCveList, cur_time:",
			common.GetCurTime(), ",err: ", err)
	}
	return psl, err
}

func QueryOpenGaussCveByNum(osl *OpenGaussCveList, field ...string) error {
	o := orm.NewOrm()
	err := o.Read(osl, field...)
	return err
}

func QueryGaussVersionByCid(cveId int64) (ogv []OpenGaussVersion, err error) {
	sql := `select * from cve_open_gauss_version where cve_id = %d order by repo_version desc`
	sql = fmt.Sprintf(sql, cveId)
	o := orm.NewOrm()
	num, err := o.Raw(sql).QueryRows(&ogv)
	logs.Info("QueryGaussVersionByCid, err: ", err, num)
	return
}
